import sqlalchemy
from sqlalchemy import create_engine , ForeignKey
from sqlalchemy import Column, Integer, String, Text 
from sqlalchemy.orm import sessionmaker, relation, backref 
from pprint import  pprint
from sqlalchemy.ext.declarative import declarative_base 

from sqlalchemy import UniqueConstraint

import validate

# TODO: change table names to simple plurals

#===============================================================================
# GLOBALS
#===============================================================================
Base = declarative_base()
engine = create_engine('sqlite:///form_2.db', echo=False)
Session = sessionmaker(bind=engine)
s = Session()
metadata = Base.metadata

#===============================================================================
# ACCESS
# Class consists of helper functions to simplify accessing data objects with
# simpler method/function calls
#===============================================================================

def session_open():
    Session = sessionmaker(bind=engine)
    return Session()

def commit():
    try:
        s.commit()
        content = 'OK'
        valid = True   
    except sqlalchemy.exc.InvalidRequestError, e:
        content = e.args
        valid = False
    except sqlalchemy.exc.IntegrityError, e:
        content =  e.args
        content = 'A record with this name or other unique identifier already exists'
        valid = False

    return valid, content
    
class Access():
    def save(self):
        '''If object has a "name" field, then validate it''' 
        valid = True
        content = self
        if 'name' in self.__dict__.keys():
            valid, content = validate.name(self.name)
        if valid:
            s.add(self)
        #TODO validate that names are text no spaces
        return valid, content
        
    def read(self):
        '''Reads object from db and  returns it. Requires that a "name" column 
        is used as unique identifier.'''
  
        filter_string = '{0}.name="{1}"'.format(self.__table__, self.name)
        # return s.query(self.__class__).filter(filter_string).one()
        try:
            object = s.query(self.__class__).filter(filter_string).one()
        except sqlalchemy.orm.exc.NoResultFound, e:
            object = None
        return object
        
    def all(self):
        return s.query(self.__class__).all()
    
    def delete(self):
        s.delete(self)
        
    def select(self, selection):
        '''Returns a selection of objects. Examples of filters are:
        "_group.id < 4"
        '''
        return s.query(self.__class__).filter(selection).all()

            
    def exists(self):
        '''Returns true if selection returns one or more records'''
        filter_string = '{0}.name="{1}"'.format(self.__table__, self.name)
        return bool(s.query(self.__class__).filter(filter_string).count() > 0)

    def create(self):
        #TEST: the create function
        if self.exists():
            return False, "A record with this name or other unique identifier already exists"
        else:
            return self.save()
        
    def add(self,type,id):
        '''General method for linking one object to another'''
#        print 5, self.__repr__()
        valid = False
        content = 'Method "add" not implemented for adding {0} to {1}'.format(
                                                self.__class__.__name__, type)
        if self.__class__.__name__ == 'User':# Adding a user to a groups
            if type == 'Group':
                #group = Group(name).read()
                u2g = User2Group(self.id, id)
                valid, content = u2g.save()       
        return valid, content
            

#===============================================================================
#    
# DATA MODEL 
# 
#===============================================================================


    
class User(Base, Access):
    __module__ = __name__
    __tablename__ = 'user'
    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(24), unique = True) 
    email = Column('email', String(255))
    password = Column('password',String(255))
 
    def __init__(self, name=None):
        self.name = name
    def __repr__(self):
        return '<User %s "%s">' % (self.id, self.name)


       
class Group(Base, Access):
    __module__ = __name__
    __tablename__ = '_group'
    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(24), unique = True, nullable=False)
    
    def __init__(self, name=None):
        self.name = name

    def __repr__(self):
        return '<Group %s "%s">' % (self.id, self.name)
    


if __name__ == '__main__':
    pass
###  CREATE TABLES / SESSION
    # metadata.create_all(engine)
    
